package barney.davis.hume.it3048todo.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import barney.davis.hume.it3048todo.constants.TaskRepeat;
import barney.davis.hume.it3048todo.constants.TaskStatus;
import barney.davis.hume.it3048todo.dto.Task;

public class TaskDAO extends SQLiteOpenHelper implements ITaskDAO {

	// static variables for database
	// database name
	private static final String DB_NAME = "toDoList.db";
	// database version number
	private static final int DB_VERSION = 1;
	// table name
	private static final String TBL_TASK = "task";
	// column names
	private static final String KEY_ID = "id";
	private static final String KEY_TEXT = "taskText";
	private static final String KEY_DUE_DATE = "dueDate";
	private static final String KEY_COMPLEXITY = "complexity";
	private static final String KEY_STATUS = "status";
	private static final String KEY_REPEAT = "repeat";
	private static final String KEY_LOCATION_LABEL = "location_label";
	private static final String KEY_LATITUDE = "latitude";
	private static final String KEY_LONGITUDE = "longitude";
	// indexes for the columns
	private static final int COL_ID = 0;
	private static final int COL_TEXT = 1;
	private static final int COL_DUE_DATE = 2;
	private static final int COL_COMPLEXITY = 3;
	private static final int COL_STATUS = 4;
	private static final int COL_REPEAT = 5;
	private static final int COL_LOCATION_LABEL = 6;
	private static final int COL_LATITUDE = 7;
	private static final int COL_LONGITUDE = 8;

	public TaskDAO(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		// TODO Auto-generated constructor stub
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void save(Task task) throws Exception {
		if ((task.getDueDate().equals("")) || (task.getDueDate() == null)) {
			throw new IllegalArgumentException("Due date cannot be empty");
		}

		
		String insertOrUpdateTask = ("INSERT OR REPLACE INTO " + TBL_TASK + " (" + KEY_ID + ", " + KEY_TEXT + ", "
				+ KEY_DUE_DATE + ", " + KEY_COMPLEXITY + ", " + KEY_STATUS + ", " + KEY_REPEAT + ", "
				+ KEY_LOCATION_LABEL + ", " + KEY_LATITUDE + ", " + KEY_LONGITUDE + ") VALUES ('" + task.getID()
				+ "', '" + task.getText() + "', " + task.getDueDate().getTimeInMillis() + ", '" + task.getComplexity()
				+ "', '" + task.getStatus() + "', '" + task.getRepeat() + "', '" + task.getLocationLabel() + "', '"
				+ task.getLatitude() + "', '" + task.getLongitude() + "')");

		SQLiteDatabase db = this.getReadableDatabase();
		db.execSQL(insertOrUpdateTask);
		db.close();

	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public Task fetch(String ID) throws Exception {
		SQLiteDatabase db = this.getReadableDatabase();
		String fetchQuery = "SELECT * FROM " + TBL_TASK + " WHERE ID='" + ID + "'";

		Cursor cursor = db.rawQuery(fetchQuery, null);
		// query should return just one task.
		cursor.moveToFirst();
		Task rtrn = getCurrentTask(cursor);
		// clean up and return
		cursor.close();
		db.close();
		return rtrn;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public ArrayList<Task> fetchByStatus(TaskStatus status) throws Exception {
		SQLiteDatabase db = this.getReadableDatabase();
		String fetchQuery = "Select * FROM " + TBL_TASK + " WHERE " + KEY_STATUS + "='" + status + "'";
		Cursor cursor = db.rawQuery(fetchQuery, null);

		cursor.moveToFirst();
		ArrayList<Task> rtrn = new ArrayList<Task>();
		for (int i = 0; i < cursor.getCount(); i++) {
			Task task = getCurrentTask(cursor);
			rtrn.add(task);
		}
		// clean up and return
		cursor.close();
		db.close();
		return rtrn;
	}

// this whole method has got to go, too many issues. Starting from scratch.
	
//	@Override
//	public ArrayList<Task> fetchByDueDate(Calendar date) throws Exception {
//		Long dateLong = date.getTimeInMillis();
//		SQLiteDatabase db = this.getReadableDatabase();
//		// create a new Calendar object with the same Y/M/D as the date
//		// argument, but a time of 00:00:00
//		Calendar startDate = new GregorianCalendar();
//		startDate.clear();
//		startDate.set(date.get(Calendar.YEAR), date.get(Calendar.MONTH), date.get(Calendar.DATE));
//		// create a new Calendar object with a Y/M/D that is one day higher than
//		// the argument date, but a time of 00:00:00
//		Calendar endDate = new GregorianCalendar();
//		endDate.clear();
//		endDate.set(date.get(Calendar.YEAR), date.get(Calendar.MONTH), date.get(Calendar.DATE) + 1);
//		Long startDateLong = startDate.getTimeInMillis();
//		Long endDateLong = endDate.getTimeInMillis();
//		// Fetch all tasks from the database where endDate > argument date >
//		// startDate
//		String fetchQuery = "Select * FROM " + TBL_TASK + " WHERE (" + KEY_DUE_DATE + ">=" + startDateLong + ") AND ("
//				+ KEY_DUE_DATE + "<" + endDateLong + ")";
//		Cursor cursor = db.rawQuery(fetchQuery, null);
//		// load the return ArrayList
//		cursor.moveToFirst();
//		ArrayList<Task> rtrn = new ArrayList<Task>();
//		for (int i = 0; i < cursor.getCount(); i++) {
//			Task task = getCurrentTask(cursor);
//			rtrn.add(task);
//		}
//		// clean up and return
//		cursor.close();
//		db.close();
//		return rtrn;
//	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public ArrayList<Task> fetchAll() throws Exception {

		// select all tasks from database
		SQLiteDatabase db = this.getReadableDatabase();
		String fetchQuery = "SELECT * FROM " + TBL_TASK;
		Cursor cursor = db.rawQuery(fetchQuery, null);

		// Put all of the tasks into an ArrayList<Task>
		cursor.moveToFirst();
		ArrayList<Task> rtrn = new ArrayList<Task>();
		/*
		 * for (int i = 0; i < cursor.getCount(); i++) { Task task =
		 * getCurrentTask(cursor); rtrn.add(task); }
		 */
		// while (cursor.moveToNext())
		do {
			Task task = getCurrentTask(cursor);
			rtrn.add(task);
		} while (cursor.moveToNext());
		// clean up and return the ArrayList
		cursor.close();
		db.close();
		return rtrn;

	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void delete(Task task) throws Exception {
		String ID = task.getID();
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteQuery = "DELETE FROM " + TBL_TASK + " WHERE id='" + ID + "'";
		db.execSQL(deleteQuery);
		//Cursor cursor = db.rawQuery(deleteQuery, null);
		//db.delete(TBL_TASK, COL_ID + " = ?", new String[] {);

		// clean up
		//cursor.close();
		db.close();
	}

	/**
	 * {@inheritDoc}
	 */
	private Task getCurrentTask(Cursor cursor) throws Exception {
		// get the task ID
		String taskID = cursor.getString(COL_ID);

		// get the task text
		String taskText = cursor.getString(COL_TEXT);

		// get the due date (as a long) and convert it back to GregorianCalendar
		Long dateLong = cursor.getLong(COL_DUE_DATE);
		Calendar taskDate = new GregorianCalendar();
		taskDate.setTimeInMillis(dateLong);

		// get the complexity
		double taskComplexity = cursor.getDouble(COL_COMPLEXITY);

		// get the status. Catch IllegalArgumentException (status is allowed to
		// be null)
		TaskStatus taskStatus;
		try {
			taskStatus = TaskStatus.valueOf(cursor.getString(COL_STATUS));
		} catch (IllegalArgumentException e) {
			taskStatus = null;
			// e.printStackTrace();
		}
		// get the repeat. Catch IllegalArgumentException (repeat is allowed to
		// be null)
		TaskRepeat taskRepeat;

		try {
			taskRepeat = TaskRepeat.valueOf(cursor.getString(COL_REPEAT));
		} catch (IllegalArgumentException e) {
			taskRepeat = null;
			// e.printStackTrace();
		}

		// get the GPS location. Catch IllegalArgumentException (location is
		// allowed to be null)

		Location location = new Location((String) null);

		try {
			Double latitude = cursor.getDouble(COL_LATITUDE);
			Double longitude = cursor.getDouble(COL_LONGITUDE);
			location.setLatitude(latitude);
			location.setLongitude(longitude);
		} catch (IllegalArgumentException e) {
			location = null;
		}

		// get the location label. Catch IllegalArgumentException (locationLabel
		// is allowed to be null)

		String locationLabel = new String();
		try {
			locationLabel = cursor.getString(COL_LOCATION_LABEL);
		} catch (IllegalArgumentException e) {
			locationLabel = null;
		}
		Task rtrn = new Task(taskID, taskText, taskDate, taskComplexity, taskStatus, taskRepeat, location,
				locationLabel);
		return rtrn;

	}

	/**
	 * Convenience method for testing. This query deletes all rows in the
	 * database.
	 */
	public void deleteAllRecords() {
		SQLiteDatabase db = this.getReadableDatabase();
		String deleteQuery = "DELETE FROM " + TBL_TASK;
		db.execSQL(deleteQuery);
		db.close();
		
	}

	/**
	 * Creates database
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		String createTaskTable = ("CREATE TABLE " + TBL_TASK + " (" + KEY_ID + " TEXT PRIMARY KEY, " + KEY_TEXT
				+ " TEXT, " + KEY_DUE_DATE + " INTEGER, " + KEY_COMPLEXITY + " REAL, " + KEY_STATUS + " TEXT, "
				+ KEY_REPEAT + " TEXT, " + KEY_LOCATION_LABEL + " TEXT, " + KEY_LATITUDE + " REAL, " + KEY_LONGITUDE + " REAL);");
		db.execSQL(createTaskTable);
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public ArrayList<Task> fetchByDueDate(Calendar firstDate, Calendar lastDate) throws Exception {
		SQLiteDatabase db = this.getReadableDatabase();
		// create a new Calendar object with the same Y/M/D as the date
		// argument, but a time of 00:00:00
		Calendar startDate = new GregorianCalendar();
		startDate.clear();
		startDate.set(firstDate.get(Calendar.YEAR), firstDate.get(Calendar.MONTH), firstDate.get(Calendar.DATE));
		// create a new Calendar object with a Y/M/D that is one day higher than
		// the argument date, but a time of 00:00:00
		Calendar endDate = new GregorianCalendar();
		endDate.clear();
		endDate.set(lastDate.get(Calendar.YEAR), lastDate.get(Calendar.MONTH), lastDate.get(Calendar.DATE) + 1);
		Long startDateLong = startDate.getTimeInMillis();
		Long endDateLong = endDate.getTimeInMillis();
		// Fetch all tasks from the database where endDate > argument date >
		// startDate
		String fetchQuery = "Select * FROM " + TBL_TASK + " WHERE (" + KEY_DUE_DATE + ">=" + startDateLong + ") AND ("
				+ KEY_DUE_DATE + "<" + endDateLong + ")";
		Cursor cursor = db.rawQuery(fetchQuery, null);
		// load the return ArrayList
		cursor.moveToFirst();
		ArrayList<Task> rtrn = new ArrayList<Task>();
		for (int i = 0; i < cursor.getCount(); i++) {
			Task task = getCurrentTask(cursor);
			rtrn.add(task);
		}
		// clean up and return
		cursor.close();
		db.close();
		return rtrn;
	}

}
